﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using BIS.Portal.Modules.CMS.Entity;
using BIS.Utility.Converter;
using BIS.Utility.DAL;
using BIS.Utility.DAL.DataMapper;

namespace BIS.Portal.Modules.CMS.DAL
{
    public class ClientCmsArticleDAL
    {
        #region Properties chỉ lấy được khi gọi hàm GetArticleListDisplay trước

        private static string _categoryName;
        private static int _articleCount;

        public static string CategoryName
        {
            get { return _categoryName; }
            set { _categoryName = value; }
        }

        public static int ArticleCount
        {
            get { return _articleCount; }
            set { _articleCount = value; }
        }

        #endregion
        /// <summary>
        /// Lấy tin tức nổi bật theo loại tin
        /// </summary>
        /// <param name="iIs_hot_articlet"></param>
        /// <param name="iLanguage_in_use_id"></param>
        /// <param name="iCategory_id"></param>
        /// <param name="iNumber_artcicle_display"></param>
        /// <returns></returns>
        public static IList<ClientCmsArticleAndCategoryEntity> GetArticleByHotArticle(int iIs_hot_articlet,int iLanguage_in_use_id, int iCategory_id,int iNumber_artcicle_display)
        {
            IList<ClientCmsArticleAndCategoryEntity> list;
            SqlParameter[] _param = new SqlParameter[] 
                                        {   new SqlParameter("@is_hot_article", iIs_hot_articlet),
                                            new SqlParameter("@language_in_use_id", iLanguage_in_use_id),
                                            new SqlParameter("@category_id", iCategory_id),
                                            new SqlParameter("@number_artcicle_display", iNumber_artcicle_display)
                                        };


            using (IDataReader reader = SqlHelper.ExecuteReader(DALConnection.ConnectionString,
                                        CommandType.StoredProcedure,
                                        "BIS_sCMS_ARTILCE_DISPLAY_SelectByIsHot",
                                        _param))
            {
                list = EntityMapper.FillCollection<ClientCmsArticleAndCategoryEntity>(reader);
            }

            return list;
        }
        /// <summary>
        /// Lấy tin mới được orderby post_date and last_date desc
        /// </summary>
        /// <param name="iLanguage_in_use_id"></param>
        /// <param name="iCategory_id"></param>
        /// <param name="iNumber_artcicle_display"></param>
        /// <returns></returns>
        public static IList<ClientCmsArticleAndCategoryEntity> GetArticleNewByDate(int iLanguage_in_use_id, int iCategory_id,int iNumber_artcicle_display)
        {
            IList<ClientCmsArticleAndCategoryEntity> list;
            SqlParameter[] _param = new SqlParameter[] 
                                        {   new SqlParameter("@category_id", iCategory_id),
                                            new SqlParameter("@language_in_use_id", iLanguage_in_use_id),
                                            new SqlParameter("@number_artcicle_display", iNumber_artcicle_display)
                                        };

            using (IDataReader reader = SqlHelper.ExecuteReader(DALConnection.ConnectionString,
                                                             CommandType.StoredProcedure, "BIS_sCMS_ARTICLE_DISPLAY_SelectOrderByPostDateLastDate", _param))
            {
                list = EntityMapper.FillCollection<ClientCmsArticleAndCategoryEntity>(reader);
            }
            return list;
        }
        /// <summary>
        /// Lấy tin tức nổi bật theo nhóm đặc biệt
        /// </summary>
        /// <param name="iIs_hot_articlet"></param>
        /// <param name="iLanguage_in_use_id"></param>
        /// <param name="display_type_id"></param>
        /// <param name="iNumber_artcicle_display"></param>
        /// <returns></returns>
        public static IList<ClientCmsArticleAndCategoryEntity> GetArticleDisplayTypeByHot(int iIs_hot_articlet,int iLanguage_in_use_id,int display_type_id, int iNumber_artcicle_display)
        {

            IList<ClientCmsArticleAndCategoryEntity> list;
            SqlParameter[] _param = new SqlParameter[] 
                                        {   new SqlParameter("@is_hot_article", iIs_hot_articlet),
                                            new SqlParameter("@language_in_use_id", iLanguage_in_use_id),
                                            new SqlParameter("@display_type_id", display_type_id),
                                            new SqlParameter("@number_artcicle_display", iNumber_artcicle_display)
                                        };


            using (IDataReader reader = SqlHelper.ExecuteReader(DALConnection.ConnectionString,
                                        CommandType.StoredProcedure,
                                        "BIS_sCMS_ARTILCE_DISPLAY_TYPE_SelectByHot",
                                        _param))
            {
                list = EntityMapper.FillCollection<ClientCmsArticleAndCategoryEntity>(reader);
            }

            return list;
        }
        /// <summary>
        /// Lấy tin tức mới theo nhóm đặc biệt được order by post_date and last date desc
        /// </summary>
        /// <param name="iLanguage_in_use_id"></param>
        /// <param name="display_type_id"></param>
        /// <param name="iNumber_artcicle_display"></param>
        /// <returns></returns>
        public static IList<ClientCmsArticleAndCategoryEntity> GetArticleNewDisplayTypeOrderByDate(int iLanguage_in_use_id,int display_type_id, int iNumber_artcicle_display)
        {
            IList<ClientCmsArticleAndCategoryEntity> list;
            SqlParameter[] _param = new SqlParameter[] 
                                        {   new SqlParameter("@language_in_use_id", iLanguage_in_use_id),
                                            new SqlParameter("@display_type_id", display_type_id),
                                            new SqlParameter("@number_artcicle_display", iNumber_artcicle_display)
                                        };


            using (IDataReader reader = SqlHelper.ExecuteReader(DALConnection.ConnectionString,
                                        CommandType.StoredProcedure,
                                        "BIS_sCMS_ARTILCE_DISPLAY_TYPE_SelectOrderByDate",
                                        _param))
            {
                list = EntityMapper.FillCollection<ClientCmsArticleAndCategoryEntity>(reader);
            }

            return list;
        }
        public static IList<ClientCmsArticleAndCategoryEntity> GetArticleDisplayRelated(int iCategory_id, int iLanguage_in_use_id, int iNumber_artcicle_display, int iArticle_id)
        {
            IList<ClientCmsArticleAndCategoryEntity> list;
            SqlParameter[] _param = new SqlParameter[] 
                                        {   new SqlParameter("@category_id", iCategory_id),
                                            new SqlParameter("@language_in_use_id", iLanguage_in_use_id),
                                            new SqlParameter("@number_artcicle_display",iNumber_artcicle_display ),
                                            new SqlParameter("@article_id",iArticle_id)
                                        };
            using (IDataReader reader = SqlHelper.ExecuteReader(DALConnection.ConnectionString,
                                        CommandType.StoredProcedure,
                                        "BIS_sCMS_ARTICLE_DISPLAY_SelectArticleRelated",
                                        _param))
            {
                list = EntityMapper.FillCollection<ClientCmsArticleAndCategoryEntity>(reader);
            }

            return list;
        }
        /// <summary>
        /// Lấy danh sách dữ liệu tin
        /// </summary>
        /// <param name="iCategory_id"></param>
        /// <param name="iLanguage_in_use_id"></param>
        /// <param name="iPage_index"></param>
        /// <param name="iNum_rows"></param>
        /// <returns></returns>
        public static IList<ClientCmsArticleAndCategoryEntity> GetArticleListDisplay(int iCategory_id, int iLanguage_in_use_id, int iPage_index, int iNum_rows)
        {
            IList<ClientCmsArticleAndCategoryEntity> list;
            SqlParameter[] _param = new SqlParameter[]
                                        {
                                            new SqlParameter("@category_id", iCategory_id),
                                            new SqlParameter("@language_in_use_id", iLanguage_in_use_id),
                                            new SqlParameter("@category_name", SqlDbType.NVarChar,200)
                                                {
                                                    Direction = ParameterDirection.Output
                                                },
                                            new SqlParameter("@article_count",SqlDbType.Int)
                                                {
                                                    Direction = ParameterDirection.Output
                                                },
                                            new SqlParameter("@page_index",iPage_index),
                                            new SqlParameter("@num_rows",iNum_rows), 
                                        };
            using (IDataReader reader = SqlHelper.ExecuteReader(DALConnection.ConnectionString,
                                        CommandType.StoredProcedure,
                                        "BIS_sCMS_ARTICLE_DISPLAY_GetArticleByCategoryId",
                                        _param))
            {
                list = EntityMapper.FillCollection<ClientCmsArticleAndCategoryEntity>(reader);

            }
           // CategoryName = (string)_param[2].Value;
            ArticleCount = (int)_param[3].Value;
            return list;

        }
        /// <summary>
        /// Lấy dữ liệu từ Article theo Article_id
        /// </summary>
        /// <param name="iLanguage_in_use"></param>
        /// <param name="iArticleId"></param>
        /// <returns></returns>
        public static ClientCmsArticleDetailsEntity GetArticleByArticleId(int iLanguage_in_use, int iArticleId)
        {
            ClientCmsArticleDetailsEntity objArticle = null;
            SqlParameter[] _param = new SqlParameter[] { new SqlParameter("@language_in_use_id", iLanguage_in_use), new SqlParameter("@article_id", iArticleId), };
            using (IDataReader reader = SqlHelper.ExecuteReader(DALConnection.ConnectionString,
                                       CommandType.StoredProcedure,
                                       "BIS_sCMS_ARTICLE_DISPLAY_GetArticleByArticleId", _param))
            {
                objArticle = EntityMapper.FillObject<ClientCmsArticleDetailsEntity>(reader);

            }
            return objArticle;
        }
        /// <summary>
        /// Lấy danh sách tin khác sau tin tin tin chi tiết
        /// </summary>
        /// <param name="iArticle_id"></param>
        /// <param name="iLanguage_in_use_id"></param>
        /// <param name="iNumber_rows"></param>
        /// <returns></returns>
        public static IList<ClientCmsArticleAndCategoryEntity> GetOtherArticleExcludeArcitcleDetails(int iArticle_id, int iLanguage_in_use_id, int iNumber_rows)
        {
            IList<ClientCmsArticleAndCategoryEntity> list;
            SqlParameter[] _param = new SqlParameter[] 
                                        {   new SqlParameter("@article_id", iArticle_id),
                                            new SqlParameter("@language_in_use_id", iLanguage_in_use_id),
                                            new SqlParameter("@number_rows",iNumber_rows)
                                        };


            using (IDataReader reader = SqlHelper.ExecuteReader(DALConnection.ConnectionString,
                                        CommandType.StoredProcedure,
                                        "BIS_sCMS_ARTICLE_DISPLAY_GetOtherArticleExcludeArcitcleDetails",
                                        _param))
            {
                list = EntityMapper.FillCollection<ClientCmsArticleAndCategoryEntity>(reader);
            }

            return list;
        }
        /// <summary>
        /// lấy danh sách tin liên quan theo article_id
        /// </summary>
        /// <param name="iArticle_id"></param>
        /// <param name="iLanguage_in_use_id"></param>
        /// <param name="iNumber_rows"></param>
        /// <returns></returns>
        public static IList<ClientCmsArticleAndCategoryEntity> GetListRelatedArticle(int iArticle_id, int iLanguage_in_use_id, int iNumber_rows)
        {
            IList<ClientCmsArticleAndCategoryEntity> list;
            SqlParameter[] _param = new SqlParameter[] 
                                        {   new SqlParameter("@article_id", iArticle_id),
                                            new SqlParameter("@language_in_use_id", iLanguage_in_use_id),
                                            new SqlParameter("@number_rows",iNumber_rows)
                                        };


            using (IDataReader reader = SqlHelper.ExecuteReader(DALConnection.ConnectionString,
                                        CommandType.StoredProcedure,
                                        "BIS_sCMS_ARTICLE_DISPLAY_GetListRelatedArticle",
                                        _param))
            {
                list = EntityMapper.FillCollection<ClientCmsArticleAndCategoryEntity>(reader);
            }

            return list;
        }
        /// <summary>
        /// Search theo keyword,ngay tháng và loại tin
        /// </summary>
        /// <param name="sKeyword"></param>
        /// <param name="dFrom_date"></param>
        /// <param name="dTo_date"></param>
        /// <param name="iLanguage_in_use_id"></param>
        /// <param name="iPage_index"></param>
        /// <param name="iNumber_rows"></param>
        /// <param name="iCategory_id"></param>
        /// <returns></returns>
        public static IList<ClientCmsArticleAndCategoryEntity> GetArticleAdvanceSearch(string sKeyword, DateTime dFrom_date, DateTime dTo_date, int iLanguage_in_use_id, int iPage_index, int iNumber_rows, int iCategory_id)
        {
     
            IList<ClientCmsArticleAndCategoryEntity> list;
            SqlParameter[] _param = new SqlParameter[8];
            _param[0] = new SqlParameter("@keyword", sKeyword);
            _param[1] = new SqlParameter("@from_date", dFrom_date);
            _param[2] = new SqlParameter("@to_date", dTo_date);
            _param[3] = new SqlParameter("@language_in_use_id", iLanguage_in_use_id);
            _param[4] = new SqlParameter("@article_count", SqlDbType.Int)
                                               {
                                                   Direction = ParameterDirection.Output
                                               };
            _param[5] = new SqlParameter("@page_index", iPage_index);
            _param[6] = new SqlParameter("@number_rows", iNumber_rows);
            _param[7] = new SqlParameter("@category_id", iCategory_id);
            
            using (IDataReader reader = SqlHelper.ExecuteReader(DALConnection.ConnectionString, CommandType.StoredProcedure, "BIS_sCMS_ARTICLE_DISPLAY_AdvanceSearch", _param))
            {
                list = EntityMapper.FillCollection<ClientCmsArticleAndCategoryEntity>(reader);
            }
            _articleCount = (int) _param[4].Value;
            return list;
        
        }
        /// <summary>
        /// Lấy madia theo article_id
        /// </summary>
        /// <param name="iArticle_id"></param>
        /// <param name="iLanguage_in_use_id"></param>
        /// <param name="iMedia_type"></param>
        /// <param name="iPage_index"></param>
        /// <param name="iNumber_rows"></param>
        /// <param name="iCount"></param>
        /// <returns></returns>
        public static IList<ClientCmsMediaEnity> GetCmsMediaDetailByArticleId(int iArticle_id, int iLanguage_in_use_id, int iMedia_type, int iPage_index, int iNumber_rows,ref int iCount)
        {
            IList<ClientCmsMediaEnity> list;
            SqlParameter[] _param = new SqlParameter[] 
                                        {   new SqlParameter("@article_id", iArticle_id),
                                            new SqlParameter("@language_in_use_id", iLanguage_in_use_id),
                                            new SqlParameter("@media_type",iMedia_type),
                                            new SqlParameter("@page_index",iPage_index),
                                            new SqlParameter("@number_rows",iNumber_rows),
                                            new SqlParameter("@Count",SqlDbType.Int)
                                                {
                                                    Direction = ParameterDirection.Output
                                                }
                                        };


            using (IDataReader reader = SqlHelper.ExecuteReader(DALConnection.ConnectionString,
                                        CommandType.StoredProcedure,
                                        "BIS_sCMS_MEDIA_DISPLAY_GetCmsMediaDetailByArticleId",
                                        _param))
            {
                list = EntityMapper.FillCollection<ClientCmsMediaEnity>(reader);
            }
            iCount= (int) _param[5].Value;
            return list;
        }
        ///// <summary>
        ///// Lấy Media type theo Article_id
        ///// </summary>
        ///// <param name="iArticleId"></param>
        ///// <returns></returns>
        //public static IList<int> GetMediaTypeByArticleId(int iArticleId)
        //{
        //    IList<int> list=null;
        //    IDataReader reader = SqlHelper.ExecuteReader(DALConnection.ConnectionString,
        //                                                 CommandType.StoredProcedure,
        //                                                 "BIS_sCMS_MEDIA_DISPLAY_GetMediaTypeByArticleId",
        //                                                 new SqlParameter("@article_id", iArticleId));
        //    while (reader.Read())
        //    {
        //        int iMediaType = Functions.GetInt(reader["media_type"]);
        //        list.Add(iMediaType);
        //    }
        //    return list;
        //}
        /// <summary>
        /// Lấy top madia entity theo article_id
        /// </summary>
        /// <param name="iArticle_id"></param>
        /// <param name="iLanguage_in_use_id"></param>
        /// <param name="iMedia_type"></param>
        /// <param name="iNumber_rows"></param>
        /// <returns></returns>
        public static IList<ClientCmsMediaEnity> GetCmsTopMediaEnitiesByArticleId(int iArticle_id, int iLanguage_in_use_id, int iMedia_type, int iNumber_rows)
        {
            IList<ClientCmsMediaEnity> list;
            SqlParameter[] _param = new SqlParameter[] 
                                        {   new SqlParameter("@article_id", iArticle_id),
                                            new SqlParameter("@language_in_use_id", iLanguage_in_use_id),
                                            new SqlParameter("@media_type",iMedia_type),
                                            new SqlParameter("@number_rows",iNumber_rows)
                                        };


            using (IDataReader reader = SqlHelper.ExecuteReader(DALConnection.ConnectionString,
                                        CommandType.StoredProcedure,
                                        "BIS_sCMS_MEDIA_DISPLAY_GetCmsTopMediaDetailByArticleId",
                                        _param))
            {
                list = EntityMapper.FillCollection<ClientCmsMediaEnity>(reader);
            }
            return list; 
        }
        /// <summary>
        /// Lấy danh sách Article không thuộc nhóm category nào
        /// </summary>
        /// <param name="iLanguage_in_use_id"></param>
        /// <returns></returns>
        public static IList<ClientCmsArticleFreeTextEntity> GetArticleFreeText(int iLanguage_in_use_id)
        {
            IList<ClientCmsArticleFreeTextEntity> list = null;
            SqlParameter[] _param = new SqlParameter[]
                                        {
                                            new SqlParameter("language_in_use_id", iLanguage_in_use_id),
                                            new SqlParameter("@article_id", DBNull.Value)
                                     };
            using (IDataReader reader = SqlHelper.ExecuteReader(DALConnection.ConnectionString,
                                        CommandType.StoredProcedure,
                                        "BIS_sCMS_ARTICLE_DISPLAY_GetArticleFreeText",_param))
            {
                list = EntityMapper.FillCollection<ClientCmsArticleFreeTextEntity>(reader);
            }
            return list;
        }
        /// <summary>
        /// lấy Article_content theo article free text
        /// </summary>
        /// <param name="iLanguage_in_use_id"></param>
        /// <param name="iArticle_id"></param>
        /// <returns></returns>
        public static ClientCmsArticleFreeTextEntity GetArticleContentFreeText(int iLanguage_in_use_id,int iArticle_id)
        {
            ClientCmsArticleFreeTextEntity objArticleFreeText =null;
            SqlParameter[] _param = new SqlParameter[]
                                        {
                                            new SqlParameter("language_in_use_id", iLanguage_in_use_id),
                                            new SqlParameter("@article_id", iArticle_id)
                                     };
            using (IDataReader reader = SqlHelper.ExecuteReader(DALConnection.ConnectionString,
                                        CommandType.StoredProcedure,
                                        "BIS_sCMS_ARTICLE_DISPLAY_GetArticleFreeText", _param))
            {
                objArticleFreeText = EntityMapper.FillObject<ClientCmsArticleFreeTextEntity>(reader);
            }
            return objArticleFreeText;
        }
        /// <summary>
        /// Danh sách ảnh của tin tức không thuộc category nào
        /// </summary>
        /// <param name="iLanguage_in_use_id"></param>
        /// <param name="iArticle_id"></param>
        /// <returns></returns>
        public static IList<ClientCmsArticleFreeMediaEntity> GetFreeMediaByArticleId(int iLanguage_in_use_id, int iArticle_id,int iRow_top)
        {
            IList<ClientCmsArticleFreeMediaEntity> list = null;
            SqlParameter[] _param = new SqlParameter[]
                                        {
                                            new SqlParameter("@language_in_use_id", iLanguage_in_use_id),
                                            new SqlParameter("@article_id", iArticle_id),
                                            new SqlParameter("@row_top",iRow_top)
                                     };
            using (IDataReader reader = SqlHelper.ExecuteReader(DALConnection.ConnectionString,
                                        CommandType.StoredProcedure,
                                        "BIS_sCMS_MEDIA_DISPLAY_GetFreeMediaByArticleId", _param))
            {
                list = EntityMapper.FillCollection<ClientCmsArticleFreeMediaEntity>(reader);
            }
            return list;
        }
        /// <summary>
        /// Lấy top 1 article_id theo category id
        /// </summary>
        /// <param name="iLanguage_in_use_id"></param>
        /// <param name="iCate_id"></param>
        /// <returns></returns>
        public static ClientCmsArticleIdByCategoryIdEntity GetArticleIdByCategoryId(int iLanguage_in_use_id,int iCate_id)
        {
            ClientCmsArticleIdByCategoryIdEntity obj = null;
            SqlParameter[] _param = new SqlParameter[]
                                        {
                                            new SqlParameter("@language_in_use_id", iLanguage_in_use_id),
                                            new SqlParameter("@category_id", iCate_id)
                                     };
            using (IDataReader reader = SqlHelper.ExecuteReader(DALConnection.ConnectionString,
                                        CommandType.StoredProcedure,
                                        "BIS_sCMS_ARTICLE_DISPLAY_GetArticleIdByCategoryId", _param))
            {
                obj = EntityMapper.FillObject<ClientCmsArticleIdByCategoryIdEntity>(reader);
            }
            return obj;
        }
    }
}
